# !usr/bin/env python
# -*- coding:utf-8 _*-
"""
@Author:张广勤
@Web site: https://www.tunan.wang
@Github:www.github.com
 
@File:app1.py
@Time:2022/4/15 9:55

@Motto:不积跬步无以至千里，不积小流无以成江海！
"""
'''
app1.py  books
'''

#import 第三方库
from importlib import reload
#用类做表单时可不用flash
from flask import Flask,render_template,flash,request,redirect,url_for
from flask_sqlalchemy import SQLAlchemy
from flask import send_from_directory
import os
from flask_wtf import FlaskForm
from wtforms import StringField,SubmitField
from wtforms.validators import DataRequired
#中文显示
import sys
reload(sys)
#sys.setdefaultencoding("utf-8")
sys.getdefaultencoding()

#实例化一个flask对象
app = Flask(__name__)
#数据库,在mysql中创建
#mysql -uroot -p12345
#create database flask_books charset=utf8;
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:12345@127.0.0.1:3306/flask_books'
#关闭警告信息
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = False
app.secret_key='aaabbbccc'

#实例化SQLAlchemy对象，参数为app
db = SQLAlchemy(app)

#定义表，表名，列字段，表间关系
#作者--书  relationship  反向
class Author(db.Model):
    __tablename__='authors'
    id = db.Column(db.Integer,primary_key=True)
    name=db.Column(db.String(16),unique=True)
    #作者有几本书
    books=db.relationship('Book',backref='author')

    def __repr__(self):
        return 'Author:%s' %self.name

#外键
class Book(db.Model):
    __tablename__='books'
    id =db.Column(db.Integer,primary_key=True)
    #书名不唯一!!!!!!!!!!!!!!!!
    #name = db.Column(db.String(16),unique=True)
    name = db.Column(db.String(16))
    #作者id唯一,加unique=True
    author_id=db.Column(db.Integer,db.ForeignKey('authors.id'))

    def __repr__(self):
        return 'Book: %s %s' %(self.name,self.author_id)

class AuthorForm(FlaskForm):
    author=StringField('作者',validators=[DataRequired()])
    book = StringField('书籍',validators=[DataRequired()])
    submit = SubmitField('提交')


#删除表  重新运行又回原始数据？ 实际应用中迁移？
db.drop_all()
#创建表
db.create_all()
#加入数据，提交
#有表单，可在前端加入数据
au1=Author(name='张三')
au2=Author(name='ls')
db.session.add_all([au1,au2])
db.session.commit()


bk1=Book(name='成功学',author_id=au1.id)
bk2=Book(name='djfdd',author_id=au2.id)
bk3=Book(name='kkkkkkkk',author_id=au1.id)
db.session.add_all([bk1,bk2,bk3])
db.session.commit()

#加入图标,ico/,目录下，favicon图标生成器生成，200K图片
@app.route('/favicon.ico')#设置icon
def favicon():
    return send_from_directory(os.path.join(app.root_path, 'static'),#对于当前文件所在路径,比如这里是static下的favicon.ico
                               'ico/favicon.ico', mimetype='image/vnd.microsoft.icon')

@app.route('/delete_author/<author_id>')
def delete_author(author_id):
    author=Author.query.get(author_id)
    if author:
        try:
            Book.query.filter_by(author_id=author.id).delete()
            db.session.delete(author)
            db.session.commit()
        except Exception as e:
            flash('删除作者出错')
    else:
        flash('作者找不到')

    return redirect(url_for('index'))

@app.route('/delete_book/<book_id>')
def delete_book(book_id):
    book= Book.query.get(book_id)
    if book:
        try:
            db.session.delete(book)
            db.session.commit()
        except Exception as e:
            flash('删除书籍出错')
            db.session.rollback()
    else:
        flash('书籍没找到')
    return redirect(url_for('index'))

#路由
@app.route('/',methods=['GET','POST'])
def index():
    author_form =AuthorForm()

    if author_form.validate_on_submit():
        author_name=author_form.author.data
        book_name=author_form.book.data
        author=Author.query.filter_by(name=author_name).first()
        #如果作者存在
        if author:
            #这句在所有书籍里查找,-----改为在告作者里查找
            #book = Book.query.filter_by(name=book_name).first(),两个条件，书名和作者id
            tongmin_book = Book.query.filter_by(name=book_name,author_id=author.id).first()
            #flash(tongmin_book)
            #book = Author.query.filter_by(name=book_name).first()
            #flash(book)
            #book = Author.query.filter_by(book=boo)
            #如果书籍存在,不同作者可以有同名书籍
            #if tongmin_book:
            if tongmin_book:
                flash('已存在同名书籍')
            #如果书籍不存在,填加书籍
            else:
                try:
                    new_book=Book(name=book_name,author_id=author.id)
                    db.session.add(new_book)
                    db.session.commit()
                except Exception as e:
                    print(e)
                    flash('添加书籍失败')
                    db.session.rollback()
        else:
            #作者不存在
            try:
                new_author=Author(name=author_name)
                db.session.add(new_author)
                db.session.commit()

                new_book=Book(name=book_name,author_id=new_author.id)
                db.session.add(new_book)
                db.session.commit()
            except Exception as e:
                print(e)
                flash('添加作者和书籍失败')
                db.session.rollback()

    else:
        if request.method=='POST':
            flash('参数不全')


    #查询，查询所有结果，前端根据需要显示
    #类名.query.all(), 查询所有作---对象列表
    authors=Author.query.all()
    # print(authors)
    # print(authors.name)
    # for author in authors:
    #     print(f'{author.id},{author.name}')
    #     for book in author.books:
    #         print(f'{book.name}')
    #传入对象
    return  render_template('books.html',authors=authors,form=author_form)

if __name__ =='__main__':
    app.run()


